package ru.fadeev.crammer.data;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;

import ru.fadeev.crammer.DatabaseManager;
import ru.fadeev.crammer.GFIndicesStorage;
import ru.fadeev.crammer.TestManager;
import ru.fadeev.crammer.Settings;
import ru.fadeev.crammer.Utils;


/**
 * Dictionary class represents one of the data entities of Crammer program.
 * Dictionary consists of sheets.
 * @author Konstantin Fadeyev
 *
 */
public class Dictionary implements WordsDataListener {
    // DB related fields
	private int id;
	private String name;
	private Vector<Sheet> sheets;  // vector of Sheets
	
	// non-DB related fields
	private int[] testWordsCount; 
	private boolean toTest;
	
	/**
	 * Array that contains information of how many words don't have 
	 * information about they should or should not be tested
	 */
	protected int[] uncheckedWordsCount;
	private int wordsCount;
	private WordsDataListener wordsDataListener;

	

	/**
	 * Constructs Dictionary with default initial values
	 */
	public Dictionary() {
		id = -1;
		name = "";
		sheets = null;
		
		wordsCount = 0;
		testWordsCount = new int[TestManager.TESTS_NUMBER];
		uncheckedWordsCount = new int[TestManager.TESTS_NUMBER];
		for(int i=0; i<TestManager.TESTS_NUMBER; i++) {
			testWordsCount[i] = 0;	
			uncheckedWordsCount[i] = 0;
		}
		
		toTest = false;
		wordsDataListener = null;
	}
	
	
	/**
	 * Adds sheet to Dictionary without saving to database. Used by internal
	 * functions and while importing from file.
	 * @param sheet sheet added
	 */
	void addSheet(Sheet sheet) {
		if(sheets == null) {
			sheets = new Vector<Sheet>();
		}
		sheets.addElement(sheet);		
		sheet.setWordsDataListener(this);
	}
	
	
	/**
	 * Adds sheet to dictionary and insert record in database. 
	 * Inserts only sheet record, doesn't insert word records.
	 * @param sheet object of Sheet
	 * @param storage object of GFIndicesStorage
	 */
	public synchronized void addSheet(Sheet sheet, GFIndicesStorage storage) {
		if(sheets == null) {
			loadSheets();
		}
		addSheet(sheet);
		setWordsCount(wordsCount + sheet.getWordsCount());
		for(int i=0; i<TestManager.TESTS_NUMBER; i++) {
			uncheckedWordsCount[i] += sheet.getUncheckedWordsCount(i);
		}
		
	   	try {
			Connection connection = DatabaseManager.getConnection();      
			Statement statement = 
				connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
			          ResultSet.CONCUR_UPDATABLE);
			statement.executeUpdate("INSERT INTO SHEET " +
					"(DICT_ID, " +
						"NAME, " +
						"ORDER_NUMBER) " +
					"VALUES (" + id +", '" + 
						Utils.prep(sheet.getName(), 
								Settings.SHEET_NAME_MAX_LENGTH) + "'," 
						+ (size() - 1) + ")");
	    	statement.close();
			
			CallableStatement identityStatement = 
					connection.prepareCall("{?= CALL IDENTITY()}");
			ResultSet resultSet = identityStatement.executeQuery();	
			resultSet.next();
			sheet.setId(resultSet.getInt(1));
			identityStatement.close();			
	    	connection.close();	    
    	} catch (Exception e) {
	        e.printStackTrace();
	    }		
	}
	
	
	/**
	 * Empty implementation of WordsDataListener interface function
	 */
	public void elementInserted(int index) {}
	
	
	/**
	 * Returns database table id of dictionary
	 * @return database table id of dictionary
	 */
	int getId() {
		return id;
	}
	
	
	/**
	 * Returns dictionary name
	 * @return dictionary name
	 */
	public String getName() {
		return name;
	}
	
	
	/**
	 * Returns sheet for this dictionary
	 * @param index index of sheet in dictionary
	 * @return sheet specified by index
	 */
	public Sheet getSheet(int index) {
		if(sheets == null) {
			loadSheets();
		}
		return (Sheet)sheets.elementAt(index);
	}

	
	/**
	 * Returns number of words in dictionary that need to be tested
	 * @param testType one of TestManager test types
	 * @return number of words in dictionary that need to be tested
	 */
	public int getTestWordsCount(int testType) {
		return testWordsCount[testType];
	}
	
	
	/**
	 * Returns number of words that need to be tested but only for those sheets
	 * that are chosen to test
	 * @param testType one of TestManager test types
	 * @return number of words that need to be tested in marked 'to test' sheets
	 */
	public int getToTestWordsCount(int testType) {
		int result = 0;
		for(int i=0; i < size(); i++) {
			Sheet sheet = (Sheet)sheets.elementAt(i);
			if(sheet.isToTest()) {
				result += sheet.getTestWordsCount(testType);
			}
		}
		return result;		
	}
	
	
	/**
	 * Returns total number of words in dictionary. Function doesn't load sheets or
	 * words from database, only words number.
	 * @return total number of words in dictionary
	 */
	public int getWordsCount() {
		return wordsCount;
	}

	
	/**
	 * Insert sheets into database
	 * Used while importing words data from file
	 * @param storage object of GFIndicesStorage
	 */
    void insertSheetsToDB(GFIndicesStorage storage) {
		if(sheets == null) {
			sheets = new Vector<Sheet>();
		}
		try {
	    	Connection connection = DatabaseManager.getConnection();  
	    	Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
			          ResultSet.CONCUR_UPDATABLE);
	    	CallableStatement identityStatement = connection.prepareCall("{?= CALL IDENTITY()}");
			for(int i = 0; i < size(); i++) {
				Sheet sheet = (Sheet)sheets.elementAt(i);
				
				statement.executeUpdate("INSERT INTO SHEET " +
						"(DICT_ID, " +
							"NAME, " +
							"ORDER_NUMBER) " +
						"VALUES (" + id + ", '" + 
							Utils.prep(sheet.getName(), 
									Settings.SHEET_NAME_MAX_LENGTH) + "', "
							+ i + ")");		
				ResultSet resultSet = identityStatement.executeQuery();	
				resultSet.next();
				sheet.setId(resultSet.getInt(1));			
				
				sheet.insertWordRecordsToDB(storage);
			}					
			identityStatement.close();
			statement.close();
			connection.close();
		} catch(Exception e) {
			e.printStackTrace();
		}
    }
	
	
    /**
     * Returns true if every word in dictionary was checked for test needs.
     * When Crammer starts all words are unchecked. Then process of determining 
	 * words test needs (TestNeedsWatcher) starts.
     * @param testType one of TestManager test types
     * @return true if every word in dictionary is checked
     */
	public boolean isChecked(int testType){
		return uncheckedWordsCount[testType] == 0;
	}

	
	/**
	 * Returns true if dictionary was marked as chosen to test
	 * @return true if dictionary was marked as chosen to test
	 */
	public boolean isToTest() {
		return toTest;
	}
	
	
	/**
	 * Loads sheets from database. Used only by internal functions. Sheets are 
     * loaded only at the moment when some outer object asks for them, 
     * not any time sooner.
	 */
	private void loadSheets() {
		sheets = new Vector<Sheet>();
		try {
			Connection connection = DatabaseManager.getConnection();      
			Statement statement = connection.createStatement();;
			ResultSet resultSet = statement.executeQuery(
        			"SELECT ID, NAME, " +
        			"(SELECT COUNT(*) " +
    	        		"FROM WORD_RECORD " +
    	        		"WHERE WORD_RECORD.SHEET_ID = SHEET.ID) " +
    				"AS WORDS_COUNT, TO_TEST " +
        			"FROM SHEET " +
        			"WHERE DICT_ID = " + id +
        			" ORDER BY ORDER_NUMBER"
        			);	
        	while(resultSet.next()) {
        		Sheet newSheet = new Sheet();
        		newSheet.setId(resultSet.getInt(1));
        		newSheet.setName(resultSet.getString(2));
        		int wordsCount = resultSet.getInt(3);
        		newSheet.setToTest(resultSet.getBoolean(4));
        		newSheet.setWordsCount(wordsCount);
        		for(int i=0; i<TestManager.TESTS_NUMBER; i++) {
        			newSheet.setUncheckedWordsCount(wordsCount, i);
        		}
        		addSheet(newSheet);       		
        	}
        	statement.close();
        	connection.close();
		} catch (Exception e) {
	        e.printStackTrace();
	    }		
    }

	
	/**
	 * Removes all sheets and related information from database
     * Used by dictionariesManager removing function
	 * @param storage object of GFIndicesStorage
	 */
    void removeAllSheetsFromDB(GFIndicesStorage storage) {
		if(sheets == null) {
			loadSheets();
		}
    	try {
    		for(int i=0; i < size(); i++) {
    			Sheet sheet = (Sheet)sheets.elementAt(i);
    			sheet.removeAllWordRecordsFromDB(storage);
    		}
			Connection connection = DatabaseManager.getConnection();      
			Statement statement = connection.createStatement();
			statement.executeQuery(
					"DELETE FROM SHEET " + 
					"WHERE DICT_ID = " + id
	    			);	
	    	statement.close();
	    	connection.close();
		} catch (Exception e) {
	        e.printStackTrace();
	    }		
    }
	
    
    /**
     * Removes sheet specified by 'index' and all related information 
     * from dictionary and from database.
     * @param index index of sheet to delete
     * @param storage object of GFIndicesStorage
     */
	public synchronized void removeSheet(int index, GFIndicesStorage storage) {
		Sheet sheet = getSheet(index);
		try {			
        	sheet.removeAllWordRecordsFromDB(storage);
        	
			Connection connection = DatabaseManager.getConnection();      
			Statement statement = connection.createStatement();
			statement.executeQuery(
        			"DELETE FROM SHEET " +
        			"WHERE ID = " + sheet.getId()
        			);	 
        	statement.close();
        	connection.close();
		} catch (Exception e) {
	        e.printStackTrace();
	    }	
		setWordsCount(wordsCount - sheet.getWordsCount());
		for(int i=0; i<TestManager.TESTS_NUMBER; i++) {
			uncheckedWordsCount[i] -= sheet.getUncheckedWordsCount(i);
		}
		sheets.remove(index);	
	}
	
	
	/**
	 * Sets database table id for dictionary
	 * @param value new database table id
	 */
	void setId(int value) {
		id = value;
	}
	
	/**
	 * Sets new dictionary name
	 * @param value new dictionary name
	 */
	public void setName(String value) {
		name = value;
	}
	
	/**
	 * Marks this dictionary as chosen to test (or not).
	 * @param value true if this dictionary will be chosen to test
	 */
    public void setToTest(boolean value) {
		toTest = value;
	}
    
    /**
     * Sets number of words in dictionary that haven't been checked for test 
     * needs yet. When Crammer starts, all words are unchecked. 
     * Then process of determining words test needs (TestNeedsWatcher) starts.
     * @param value number of words in dictionary that haven't been checked yet
     * @param testType one of TestManager test types
     */
	void setUncheckedWordsCount(int value, int testType) {
		uncheckedWordsCount[testType] = value;
	}
    
	/**
	 * Sets total number of words in dictionary
	 * @param value new total number of words in dictionary
	 */
	void setWordsCount(int value) {
		int change = value - wordsCount;
		wordsCount = value;
		if(wordsDataListener != null) {
			wordsDataListener.wordsCountChanged(change);
		}
	}
    	
    /**
     * Sets new WordsDataListener
     * @param listener new WordsDataListener
     */
	public void setWordsDataListener(WordsDataListener listener) {
		wordsDataListener = listener;
	}
		
	
    /**
	 * Returns number of sheets in dictionary. If sheets haven't been loaded
     * from database function loads them.
	 * @return number of sheets in dictionary
	 */
	public int size() {
		if(sheets == null) {
			loadSheets();
		}
		return sheets.size();
	}

    
	/**
     * Implements WordsDataListener interface testWordsCountChanged function
	 */
    public void testWordsCountChanged(int change, int testType, 
    		boolean unchecked, int dictionaryIndex, int sheetIndex) {
    	testWordsCount[testType] += change;
    	if(unchecked) {
    		uncheckedWordsCount[testType] -= 1;
    	}
    	if(wordsDataListener != null) {
    		wordsDataListener.testWordsCountChanged(change, testType, 
					unchecked, dictionaryIndex, sheetIndex);
		}
    }
    
    
    /**
     * This function was redefined so it returns dictionary name. 
     * Used for simpler showing it in table.
     */
	public String toString() {
    	return "<html><b>" + name + "</b></html>";
    }
	
	
	/**
	 * Update database information for sheet specified by 'index' 
	 * @param index index of sheet to update database for
	 */
    public void updateSheet(int index) {
    	Sheet sheet = getSheet(index);
    	try {
			Connection connection = DatabaseManager.getConnection();      
			Statement statement = 
				connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
			          ResultSet.CONCUR_UPDATABLE);
			statement.executeUpdate("UPDATE SHEET " +
					"SET NAME = '" + Utils.prep(sheet.getName(), 
							Settings.SHEET_NAME_MAX_LENGTH) + "'" +
					"WHERE ID = " + sheet.getId());
	    	statement.close();
	    	connection.close();	 

    	} catch (Exception e) {
	        e.printStackTrace();
	    }	
	}
    
    /**
     * Implements WordsDataListener interface testWordsCountChanged function
	 */
	public void wordsCountChanged(int change) {
		setWordsCount(wordsCount + change);
	}
}
